const { db } = require('../config/global');
const mysql = require('mysql');
// 创建链接池对象
const pool = mysql.createPool({
    ...db,
    // 最大链接数
    connectionLimit: 10
});

// 从链接池中获取一条链接
const getConn = () => {
    return new Promise((resolve) => {
        pool.getConnection((err, conn) => {
            if (err) {
                return console.error(err);
            }
            // 调用成功回调函数, 并将数据库链接对象传递给成功处理函数
            resolve && resolve(conn);
            // 释放链接
            conn.release();
        });
    });
}

/**
 * 执行sql语句
 * @param {string} sql 要执行的sql语句
 * @param {array} params sql语句中需要的参数
 * @param {function} callback 回调函数
 * */
const execute = async (sql, params = [], callback) => {
    const conn = await getConn();
    conn.query(sql, params, (err, result) => {
        if (err) {
            return console.error(err);
        }
        // 通过回调函数将查询结果传递给调用者
        callback && callback(result);
    });
}
/**
 * 查询数据
 * @param {string} sql 要执行的sql语句
 * @param {array} params 要执行的sql语句中需要的参数
 * @return {promise} 返回一个Promise对象
 * 
*/
const query = (sql, params = []) => {
    return new Promise(async (resolve) => {
        // 获取数据库链接
        const conn = await getConn();
        // 执行sql语句
        conn.query(sql, params, (err, result) => {
            if (err) {
                return console.error(err);
            }
            // 调用成功处理函数, 并将执行结果传递给成功处理函数
            resolve && resolve(result);
        });
    });
}
/**
 * 查询数据
 * @param {string} sql 要执行的sql语句
 * @param {array} params 要执行的sql语句中需要的参数
 * @return {promise} 返回一个Promise对象
 * 
*/
const queryOne = (sql, params = []) => {
    return new Promise(async (resolve) => {
        // 获取数据库链接
        const conn = await getConn();
        // 执行sql语句
        conn.query(sql, params, (err, result) => {
            if (err) {
                return console.error(err);
            }
            // 调用成功处理函数, 并将执行结果传递给成功处理函数
            resolve && resolve(result);
        });
    });
}

/**
 * 查询所有数据
 * @param {string} table 数据表名称
 * @param {object} params 页码和每页数据条数 && 查询条件
 * @param {string} order 排序方式, 默认值id desc
 * */
const getAll = async (table, params = {}, order = "id desc") => {
    let sql = `select * from ${table}`;
    const { page, size } = params;
    if (page) delete params.page;
    if (size) delete params.size;
    // 处理查询条件
    let arr = [];
    for (let key in params) {
        const value = params[key]
        if (Array.isArray()) {
            if (value.length === 2) {
                arr.push(`${key} between ${value[0]} and ${value[1]}`)
            } else {
                arr.push(`${key} in (${value.join(',')})`)
            }
        } else {
            arr.push(`${key}='${value}'`)
        }
    }
    if (arr.length > 0) {
        const map = arr.join(' and ');
        // 拼接查询条件
        if (map) {
            sql += ` where ${map}`
        }
    }
    sql += ` order by ${order}`
    if (page && size) {
        // 拼接分页语句
        sql += ` limit ${(page - 1) * size},${size}`
    }
    const result = await query(sql);
    console.log(sql, 'sql');

    return result;
};

/**
 *  查询一条数据
 * @param {string} table 要查新的数据表名
 * @param {object} where 查询条件
 * @return {object | boolean} 查询到的数据
*/
const getOne = async (table, where = {}) => {
    let sql = `select * from ${table}`;
    let arr = [];
    Object.keys(where).forEach(key => {
        const value = where[key];
        arr.push(`${key}='${value}'`);
    });
    let condition = arr.join(' and ');
    if (condition) {
        sql += ` where ${condition} limit 1`;
    }
    const result = await query(sql);
    if (result.length > 0) {
        return result[0];
    }

    return false;
};
/**
 * 新增数据
 * @param {object} data 要添加的数据
 * @param {string} table 数据表名
*/
const add = async (data, table) => {
    const arr = [];
    for (let key in data) {
        arr.push(`${key}="${data[key]}"`)
    }
    const params = arr.join(',');
    const result = await query(`insert into ?? set ${params}`, [table]);
    return result;
}

/**
 * 删除数据
 * @param {number} id 待删除数据id
 * @param {string} table 表名
 * @return {boolean} 是否删除成功
 * */
const remove = async (id, table) => {
    const result = await query(`delete from ?? where id=?`, [table, id]);
    return result;
}

/**
 * 更新数据
 * @param {number|object} map 更新条件, 可以是id也可以是一个object
 * @param {object} fields 要更新的数据
 * @param {string} table 数据表名
 * @param {object} map 查询条件, 如果传递了该条件, 则忽略id
*/
const update = async (map, fields, table) => {
    const fieldsArr = [];
    Object.keys(fields).forEach(key => fieldsArr.push(`${key}='${fields[key]}'`))
    // 要更新的字段
    const fieldsStr = fieldsArr.join(',')
    const mapArr = []
    if (typeof (map) !== 'object') {
        mapArr.push(`id=${map}`)
    } else {
        Object.keys(map).forEach(key => {
            const value = map[key]
            if (Array.isArray(value)) {
                if (value.length === 2) {
                    mapArr.push(`${key} between ${value[0]} and ${value[1]}`);
                } else {
                    mapArr.push(`${key} in (${value.join(',')})`);
                }
            } else {
                mapArr.push(`${key}='${value}'`);
            }
        });
    }
    // 更新条件
    const whereStr = mapArr.join(' and ')
    const sql = `update ${table} set ${fieldsStr} where ${whereStr}`
    const result = await query(sql);
    return result;
}


/**
 * 获取数据总条数
 * @param {string} tableName 数据表的名称
 * @param {map} object 查询条件
*/

const getCount = async (tableName, map = {}) => {
    let sql = `select count(id) as total from ${tableName}`;
    const arr = [];
    Object.keys(map).forEach(key => {
        const value = map[key];
        arr.push(`${key}='${value}'`);
    });
    let condition = arr.join(' and ');
    if (condition) {
        sql += ` where ${condition}`;
    }
    const result = await query(sql);
    return result[0]?.total;
}

/**
 * 计算字段数据总和
 * @param {string} tableName 数据表的名称
 * @param {map} object 查询条件
 * @param {field} string 统计字段
*/

const getSum = async (tableName, field, map = {}) => {
    if (!field) return false
    let sql = `select sum(${field}) as total from ${tableName}`;
    const arr = [];
    Object.keys(map).forEach(key => {
        const value = map[key]
        if (Array.isArray(value)) {
            if (value.length === 2) {
                arr.push(`${key} between ${value[0]} and ${value[1]}`);
            } else {
                arr.push(`${key} in (${value.join(',')})`);
            }
        } else {
            arr.push(`${key}='${value}'`);
        }

    });
    let condition = arr.join(' and ');
    if (condition) {
        sql += ` where ${condition}`;
    }
    const result = await query(sql);
    return result[0]?.total;
}

module.exports = {
    query,
    queryOne,
    execute,
    getAll,
    getOne,
    add,
    remove,
    update,
    getCount,
    getSum
}